package com.feng.fengDUtil;

import com.feng.fengDUtil.dao.ConnectionManager;
import com.feng.fengDUtil.dao.DbHelp;
import com.feng.fengDUtil.dao.FieldInfo;
import com.feng.fengDUtil.dao.IdHelp;
import com.feng.fengDUtil.dao.PopulateInfo;
import com.feng.fengDUtil.dao.SqlAndParams;
import com.feng.fengDUtil.dao.SqlExecutor;
import com.feng.fengDUtil.dao.TableInfo;
import com.feng.fengDUtil.dao.annotation.Table;
import com.feng.fengDUtil.dao.pojo.InsertTypeEnum;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

public class FengD {

  public ConnectionManager connectionManager;

  public FengD() {
  }

  public FengD(DataSource dataSource, boolean isSpringTransaction) {
    connectionManager = new ConnectionManager(dataSource, isSpringTransaction);
  }

  public void initTableMap(String tableSchema) {
    String sql = "select table_name from information_schema.tables where table_schema=? ";
    List<String> tables = sql(sql).list(String.class, tableSchema);
    for (String table : tables) {
      DbHelp.getTableMap().put(table, tableSchema + "." + table + " ");
    }
  }

  public <T> Page<Map> findPageList(String sql, Page<Map> page, Object... params) {
    return findPageList(Map.class, sql, page, params);
  }

/*  public <T> Page<T> findPageList(Class<T> clazz, String sql, Page<T> page, Object... params) {
    StringBuilder sb = new StringBuilder(sql);
    boolean needCalcTotal = page.getPageIndex() == 0 || page.getTotalCount() == 0;
    if (needCalcTotal) {
      int selectIndex = StringUtils.indexOfIgnoreCase(sb, "select");
      sb.insert(selectIndex + 6, " SQL_CALC_FOUND_ROWS");
    }
    sb.append(" limit ").append(page.getPageIndex() * page.getPageSize()).append(",")
        .append(page.getPageSize());
    List list = sql(sb.toString()).list(clazz, params);
    if (needCalcTotal) {
      Long total = sql("select found_rows()").one(Long.class);
      page.setTotalCount(total == null ? 0 : total.intValue());
    }
    page.setData(list);
    return page;
  }*/

  public <T> Page<T> findPageList(Class<T> clazz, String sql, Page<T> page, Object... params) {
    if ((page.getPageIndex() == 0) || (page.getTotalCount() == 0)) {
      StringBuilder sb = new StringBuilder("select count(*) ");
      if (StringUtils.countMatches(sql, " from ") == 1) {
        sb.append(sql.substring(sql.indexOf("from ")));
      } else {
        sb.append(" from (").append(sql).append(") _s");
      }
      Number total = sql(sb.toString()).one(Long.class, params);
      page.setTotalCount(total == null ? 0 : total.intValue());
    }
    StringBuilder sqlPage = new StringBuilder(sql).append(" limit ")
        .append(page.getPageIndex() * page.getPageSize()).append(",")
        .append(page.getPageSize());
    List list = sql(sqlPage.toString()).list(clazz, params);
    page.setData(list);
    return page;
  }


  public boolean isUnique(Class clazz, String fieldName, String fieldVal, Object id) {
    TableInfo tableInfo = DbHelp.getTableInfo(clazz);
    StringBuilder sb = new StringBuilder("select 1 from ")
        .append(tableInfo.getTableName()).append(" where ").append(fieldName).append("=? ");
    ArrayList params = new ArrayList();
    params.add(fieldVal);
    if (id != null) {
      sb.append(" and " + tableInfo.getIdFieldInfo().getColumnName() + " != ?");
      params.add(id);
    }
    sb.append(" limit 1");
    Long exists = sql(sb.toString()).one(Long.class, params.toArray());
    return exists == null;
  }

  public void populate(Object bean) {
    Table table = MyAnnotationUtils.findAnnotation(bean.getClass(), Table.class);
    if (table == null) {
      throw new RuntimeException(bean.getClass().getName() + "类型错误！");
    }
    List list = Arrays.asList(bean);
    populateList(list);
  }

  public <T> void populateList(Collection<T> beanList) {
    if (CollectionUtils.isEmpty(beanList)) {
      return;
    }
    Class clazz = beanList.iterator().next().getClass();
    TableInfo tableInfo = DbHelp.getTableInfo(clazz);
    List<PopulateInfo> populateList = tableInfo.getPopulateList();
    if (CollectionUtils.isEmpty(populateList)) {
      return;
    }
    FieldInfo idFieldInfo = tableInfo.getIdFieldInfo();
    List<Object> idList = beanList.stream().map(n -> idFieldInfo.getWrapValue(n))
        .collect(Collectors.toList());
    for (PopulateInfo populateInfo : populateList) {
      List subs = selectByForeignKeys(populateInfo.getBeanClass(), populateInfo.getForeignKey(),
          idList);
      Map<Object, List<Object>> kvs = TransUtil
          .transToStrList(subs, n -> Reflections.getFieldValue(n, populateInfo.getForeignKey()),
              n -> n);
      beanList.forEach(bean -> {
        Object id = idFieldInfo.getWrapValue(bean);
        Reflections.setFieldValue(bean, populateInfo.getField(), kvs.get(id));
      });
    }
  }

  private void savePopulate(Object bean) {
    TableInfo tableInfo = DbHelp.getTableInfo(bean.getClass());
    List<PopulateInfo> populateList = tableInfo.getPopulateList();
    if (populateList == null) {
      return;
    }
    Object beanId = tableInfo.getIdFieldInfo().getWrapValue(bean);
    for (PopulateInfo populateInfo : populateList) {
      List subs = (List) Reflections.getFieldValue(bean, populateInfo.getField());
      if (subs != null) {
        String where = populateInfo.getForeignKey() + "=? ";
        List params = new ArrayList();
        params.add(beanId);
        int i = 0;
        for (Object sub : subs) {
          Reflections.setFieldValue(sub, populateInfo.getForeignKey(), beanId);
          Object id = Reflections.getFieldValue(sub, "id");
          if (id != null) {
            i++;
            params.add(id);
          }
        }
        if (i > 0) {
          where += " and id not in (" + genePlaceHolders(i) + ")";
        }
        deleteByWhere(populateInfo.getBeanClass(), where, params.toArray());
        saveOrUpdateAll(subs);
      }
    }
  }


  public SqlExecutor sql(String sql) {
    return new SqlExecutor(connectionManager, sql);
  }

  public <T> T selectById(Class<T> beanClass, Object id) {
    SqlAndParams sqlAndParams = DbHelp.getSelectByIdSqlAndParams(beanClass, id);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .one(beanClass, sqlAndParams.getParams());
  }

  public String genePlaceHolders(int length) {
    return String.join(",", Collections.nCopies(length, "?"));
  }

  public <T> List<T> selectByIds(Class<T> beanClass, List ids) {
    String whereSql = " id in (" + genePlaceHolders(ids.size()) + ")";
    SqlAndParams sqlAndParams = DbHelp.getSelectAndParams(beanClass, whereSql, ids.toArray());
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .list(beanClass, sqlAndParams.getParams());
  }

  public <T> List<T> selectByForeignKeys(Class<T> beanClass, String foreignKey, List ids) {
    String whereSql = foreignKey + " in (" + genePlaceHolders(ids.size()) + ")";
    SqlAndParams sqlAndParams = DbHelp.getSelectAndParams(beanClass, whereSql, ids.toArray());
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .list(beanClass, sqlAndParams.getParams());
  }


  public <T> List<T> selectAll(Class<T> beanClass) {
    if (Map.class == beanClass) {
      throw new RuntimeException("无法得知从哪个表取出数据，该函数只适用于普通的java bean， 比如User.class这样的");
    }
    SqlAndParams sqlAndParams = DbHelp.getSelectAllSqlAndParams(beanClass);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql()).many(beanClass, new Object[0]);
  }

  public List<Map> selectAll(String tableName) {
    String sql = "select * from " + tableName;
    return new SqlExecutor(connectionManager, sql).list(Map.class, new Object[0]);
  }

  public <T> T selectByPk(Class<T> beanClass, Object[] pks) {
    SqlAndParams sqlAndParams = DbHelp.getSelectByPkSqlAndParams(beanClass, pks);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .one(beanClass, sqlAndParams.getParams());
  }

  public int insert(Object bean, InsertTypeEnum insertType) {
    SqlAndParams sqlAndParams = DbHelp.getInsertSqlAndParams(bean, insertType);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int insertWithoutNull(Object bean, InsertTypeEnum insertType) {
    SqlAndParams sqlAndParams = DbHelp.getInsertSqlAndParamsWithoutNil(bean, insertType);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int update(Object bean) {
    SqlAndParams sqlAndParams = DbHelp.getUpdateSqlAndParams(bean);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public <T> int updateAll(List<T> beans) {
    SqlAndParams sqlAndParams = DbHelp.getUpdateAllSqlAndParams(beans);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int updateWithoutNull(Object bean) {
    SqlAndParams sqlAndParams = DbHelp.getUpdateSqlAndParamsWithoutNil(bean);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int deleteById(Class tableClass, Object id) {
    SqlAndParams sqlAndParams = DbHelp.getDeleteByIdSqlAndParams(tableClass, id);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int[] deleteByIds(Class tableClass, String ids, String separator) {
    SqlAndParams sqlAndParams = DbHelp.getDeleteByIdSqlAndParams(tableClass, null);
    String[] idArray = StringUtils.split(ids, separator);
    Object[] params = new Object[idArray.length];
    System.arraycopy(idArray, 0, params, 0, idArray.length);
    return batch(sqlAndParams.getSql(), params);
  }

  public int[] deleteByIds(Class tableClass, String ids) {
    return deleteByIds(tableClass, ids, ",");
  }

  public int deleteByPk(Class tableClass, Object[] pks) {
    SqlAndParams sqlAndParams = DbHelp.getDeleteByPkSqlAndParams(tableClass, pks);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int delete(Object bean) {
    SqlAndParams sqlAndParams = DbHelp.getDeleteSql(bean);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int deleteByWhere(Class tableClass, String whereSql, Object[] params) {
    SqlAndParams sqlAndParams = DbHelp.getDeleteByWhereSql(tableClass, whereSql, params);
    return new SqlExecutor(connectionManager, sqlAndParams.getSql())
        .update(sqlAndParams.getParams());
  }

  public int[] batch(String sql, Object[][] params) {
    return new SqlExecutor(connectionManager, sql).batch(params);
  }

  public int[] batch(String sql, Object[] params) {
    Object[][] sqlParams = new Object[params.length][1];

    for (int i = 0; i < sqlParams.length; ++i) {
      sqlParams[i] = new Object[]{params[i]};
    }

    return new SqlExecutor(connectionManager, sql).batch(sqlParams);
  }

  public int[] batch(String sql, List<Object[]> params) {
    if ((params != null) && (params.size() > 0)) {
      int oneDimens = params.get(0).length;
      Object[][] sqlParams = new Object[params.size()][oneDimens];

      for (int i = 0; i < params.size(); i++) {
        sqlParams[i] = params.get(i);
      }

      return new SqlExecutor(connectionManager, sql).batch(sqlParams);
    }
    return new int[]{0};
  }

  public Connection getConnection() throws Exception {
    Connection conn = connectionManager.getConnectionOrNewFromDataSoureIfNull();
    return conn;
  }

  public Object startTranSaction(Callable callable) {
    try {
      Object transcationResult = callable.call();
      return transcationResult;
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    }
  }

  public void saveOrUpdate(Object bean) {
    TableInfo tableInfo = DbHelp.getTableInfo(bean.getClass());
    FieldInfo idField = tableInfo.getIdFieldInfo();
    if (idField != null) {
      Object id = Reflections.getFieldValue(bean, idField.getField());
      /*String idFieldName = idField.getColumnName();
      Reflections.invokeGetter(bean, idFieldName);*/
      if (id == null) {
        if (!idField.isIdGenerateByDb()) {
          IdHelp.setIdUuid(idField.getField(), bean);
        }
        id = new SqlExecutor(connectionManager, tableInfo.getInsertSql(InsertTypeEnum.insert))
            .insert(idField.getField().getType(), DbHelp.getInsertParams(tableInfo, bean));
        if (idField.isIdGenerateByDb()) {
          IdHelp.setIdValue(bean, idField.getField(), id);
        }
      } else {
        insert(bean, InsertTypeEnum.replace);
      }
      savePopulate(bean);
    } else if (tableInfo.getPkFieldList() != null) {
      insert(bean, InsertTypeEnum.replace);
    } else {
      insert(bean, InsertTypeEnum.replace);
    }
  }


  public void saveOrUpdateAll(List beans) {
    for (Object o : beans) {
      saveOrUpdate(o);
    }
  }

  /*public Object insertAutoIncre(Object bean)
    {
        SqlAndParams sqlAndParams = DbHelp.getInsertSqlAndParams(bean);
        String sql = sqlAndParams.getSql();
        Object[] params = sqlAndParams.getParams();
        boolean exception = false;
        Object id = null;
        try {
            TableInfo tableInfo = DbHelp.getTableInfo(bean.getClass());
            FieldInfo idField = tableInfo.getIdFieldInfo();
            if(idField.isIdGenerateByDb()){
                Connection conn = getConnection();
                //printSqlAndParams(sql, params);
                PreparedStatement ps = conn.prepareStatement(sql, 1);
                new QueryRunner().fillStatement(ps, params);
                ps.executeUpdate();
                ResultSet rs = ps.getGeneratedKeys();
                id = rs.next() ? rs.getObject(1) : -1L;
                IdHelp.setIdValue(bean, tableInfo.getIdFieldInfo().getField(), id);
            }else{
                IdHelp.setIdUuid(idField.getField(),bean);
                insert(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
            exception = true;
            throw new RuntimeException(e);
        }finally {
            this.connectionManager.closeConnectionByException(exception);
        }
        return id;
    }*/


  private <T> List<List<T>> partition(List<T> list, int chunkSize) {
    // fill list
    List<List<T>> lists = new ArrayList<>();
    for (int i = 0; i < list.size(); i += chunkSize) {
      int end = Math.min(list.size(), i + chunkSize);
      lists.add(list.subList(i, end));
    }
    return lists;
  }

  public <T> void insertBatch(List<T> beans, InsertTypeEnum insertType) {
    if (CollectionUtils.isEmpty(beans)) {
      return;
    }
    TableInfo tableInfo = DbHelp.getTableInfo(beans.get(0).getClass());
    FieldInfo idField = tableInfo.getIdFieldInfo();
    if (idField != null && !idField.isIdGenerateByDb()) {
      beans.forEach(n -> IdHelp.setIdUuid(idField.getField(), n));
    }
    String insertSql = tableInfo.getInsertSql(insertType);
    for (List<T> subList : partition(beans, 1000)) {
      int size = subList.size();
      Object[][] params = new Object[size][];
      for (int i = 0; i < size; i++) {
        params[i] = DbHelp.getInsertParams(tableInfo, subList.get(i));
      }
      new SqlExecutor(connectionManager, insertSql).batch(params);
    }
  }

  public <T> void insertBatch(List<Map> beans, String tableName, List<String> fieldNames) {
    if (CollectionUtils.isEmpty(beans)) {
      return;
    }
    String insertSql = MySqlUtil.geneInsertSql(tableName, fieldNames);
    for (List<Map> subList : partition(beans, 1000)) {
      int size = subList.size();
      Object[][] params = new Object[size][];
      for (int i = 0; i < size; i++) {
        params[i] = getInsertParams(fieldNames, subList.get(i));
      }
      new SqlExecutor(connectionManager, insertSql)
          .insertBatch(String.class, params);
    }
  }

  private Object[] getInsertParams(List<String> fieldNames, Map map) {
    return fieldNames.stream().map(n -> MapUtils.getObject(map, n)).toArray();
  }
}